#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2020-03-10 21:34:03
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：
import logging
import os
import re
import time_ext_
import timeEXT
import sys
APP_NAME = os.path.basename(sys.argv[0])
APP_NAME = os.path.splitext(APP_NAME)[0]
# 程序配置目录
APP_CONFIG_PATH = os.path.join(os.path.normpath(os.path.dirname(sys.argv[0])), APP_NAME)

class ParseEfl(object):
    """docstring for ParseEfl"""

    def __init__(self):
        super(ParseEfl, self).__init__()
        self.log = logging.getLogger('main.efl')

    # EFL 转dict
    def efl_to_dict(self, file):
        try:
            with open(file, 'r', encoding = 'utf-8') as f:
                data = {}
                temp = []
                for i, line in enumerate(f.readlines()):
                    string = re.findall('<(\w+)::\w+>', line, re.I)  # 表名
                    if string and string[0]:
                        temp.append([string[0]])
                    elif temp:
                        row = line.strip().replace('#\t', '').replace('@\t', '').replace('\t\t', '\t-\t').split('\t')
                        if row[1:]:
                            temp[-1].append(row)
                for i in temp:
                    tableName = i[0]
                    fields = i[1]
                    values = i[2:]
                    data.update({tableName: [dict(zip(fields, row)) for row in values]})
                return data
        except Exception as e:
            self.log.warning("解析EFL文件失败,异常:{}".format(e))

    def get_placeholder(self, database_type, i = None):
        if database_type == "oracle":
            return ":{}".format(i)
        if database_type in ["sqlite", 'access']:
            return "?"
        if database_type in ("sqlserver", "mysql", "mssql", "sql server"):
            return "%s"

    # 创建插入语句
    def create_insert_sql(self, data, msgid, database_type = "sqlite"):
        if data['Head'][0]['F_FunctionRef'] != str(msgid):
            self.log.warning("报文ID:{},不是需要的数据不能创建插入语句...".format(msgid))
            return
        sql_dict = {}
        for key, value in data.items():
            if key == "Head":
                continue
            field = ",".join(value[0].keys())
            values = [tuple(row.values()) for row in value]
            ext = ",".join((self.get_placeholder(database_type, i) for i, _ in enumerate(value[0])))
            if database_type=='sqlite':
                sql = "INSERT INTO {} ({},F_ADDTIME) VALUES ({},datetime('now', 'localtime'))".format(key, field, ext)
            else:
                sql = "INSERT INTO {} ({}) VALUES ({})".format(key, field, ext)
            sql_dict.update({key: {'sql': sql, 'values': values}})
        return sql_dict

    def create_efl(self, msgid, data, name = None):
        efl = (
                "<! Version=1.0 System=data.ygct.sample.ticket Code=utf-8 Data=1.0 !>\n"
                "<Head::Head>\n"
                "@	F_Source	F_Target	F_FunctionId	F_FunctionCode	F_FunctionRef	F_OperateType	F_Transdate	F_Error	F_Ext\n"
                "#	1	0	0	C0203	{}	2	{}	0	ygct_ticket\n"
                "</Head::Head>\n").format(msgid, timeEXT.DatetimeStrToNumber(timeEXT.GetDatetimeStr()))
        for key, value in data.items():
            if key == "Head":
                continue
            string = "@\t" + "\t".join(value[0].keys()) + '\n'
            string += "\n".join(["#\t" + "\t".join( map(str,row.values()) ) for row in value])
            efl += "<{0}::{0}>\n{1}\n</{0}::{0}>\n".format(key, string)
        path = os.path.join(APP_CONFIG_PATH,'temp')
        if not os.path.exists(path):
            os.mkdir(path)
        file_name = "{}_send_C0203.efl".format(name if name else timeEXT.DatetimeStrToNumber(timeEXT.GetDatetimeStr()))
        file_path = os.path.join(path, file_name)
        # 存存储
        with open(file_path, 'w', encoding = 'utf8') as f:
            res = f.write(efl)
            if not res:
                return
            self.log.warning("创建EFL文件存储到:{}".format(file_path))
            return file_path


# 扫描文件
def scan_files(path, lasttime = (time_ext_.time() - 48 * 3600)):
    fileDir = os.listdir(path)
    fileList = [
            os.path.join(path, file).replace('\\', '/')
            for file in fileDir
            if os.path.getmtime(os.path.join(path, file)) > lasttime
            ]
    return fileList


# 解析efl文件
def prase_efi_file(file):
    with open(file, 'r', encoding = 'utf-8') as f:
        data = []
        for i, line in enumerate(f.readlines()):
            string = re.findall('<(\w+)::\w+>', line, re.I)  # 表名
            if string and string[0]:
                data.append([string[0]])
            elif data:
                row = line.strip().replace('#\t', '').replace('@\t', '').replace('\t\t', '\t-\t').split('\t')
                if row[1:]:
                    data[-1].append(row)
        dataList = {}
        for i in data:
            if i[0] == 'Head' or len(i) == 1: continue
            tableName = i[0]
            fields = i[1]
            values = i[2:]
            dataList.setdefault(tableName, {'field': fields, 'values': values})
        return dataList


# 解析数据转换成MYSQL语句
def prase_to_mysql(data):
    sqlList = []
    for tableName, item in data.items():
        field = ','.join(item['field'])
        for row in item['values']:
            value = ','.join(["'{}'".format(v) for v in row])
            updateValue = ','.join(["{}='{}'".format(v, row[i]) for i, v in enumerate(item['field'])])
            sql = "INSERT INTO {}({}) VALUES({}) ON DUPLICATE KEY UPDATE {}".format(tableName, field, value,
                                                                                    updateValue)
            sqlList.append(sql)
    return sqlList



if __name__ == '__main__':
    # a=prase_efi_file(file)
    file = r'D:\CODE\PYTHON\项目数据\铜陵\船煤\BillFile\20200309160051147_00201.efl'
    path = r'D:\CODE\PYTHON\项目数据\铜陵\船煤\BillFile'

    data = prase_efi_file(file)
    # print(111111111,data)
    x = prase_to_mysql(data)
    print(x)
    # fileLsit=scan_files(path)
    # for file in fileLsit:
    #     print(1212121,file)
    #     data=prase_efi_file(file)
    #     prase_to_mysql(data)
    #     # break